﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using MINERP.COM;

namespace MINERP.DAL.GoodsDelivery
{
    public class GoodsDeliveryDAL
    {
        //参数
        string conStr = ConfigurationManager.ConnectionStrings["sqlCon"].ToString();
        #region 获取所有未发送的账单
        /// <summary>
        /// 获取所有未发送的账单
        /// </summary>
        /// <returns></returns>
        public DataTable GetNoDeliveryBill()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                SqlCommand cmd = new SqlCommand("Proc_GetNoDeliveryBill", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 获取数据库中最大的送货单Id
        /// <summary>
        /// 获取数据库中最大的送货单Id
        /// </summary>
        /// <returns></returns>
        public int GetMaxDeliveryId()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "SELECT  MAX(deliverId) FROM DeliveryInfo";
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                object r = cmd.ExecuteScalar();
                Type t = r.GetType();
                if (t == typeof(DBNull))
                    return 0;
                return Convert.ToInt32(r);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 插入未打印的发货信息
        /// <summary>
        /// 插入未打印的发货信息
        /// </summary>
        /// <param name="billInfos"></param>
        /// <param name="deliverId"></param>
        public void InsertDeliveryNoPrint(List<MODEL.GoodsDelivery.BillInfo> billInfos,int deliverId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                SqlTransaction tran = con.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Transaction = tran;
                try
                {
                    foreach (MODEL.GoodsDelivery.BillInfo bill in billInfos)
                    {
                        int billId = bill.BillId;
                        int noPrintStatus = (int)DeliveryStatus.NoPrint;
                        //1插入deliveryInfo
                        string sql = string.Format("insert into DeliveryInfo(deliverId,BillId,status) values({0},{1},{2})", deliverId, billId, noPrintStatus);
                        cmd.Connection = con;
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();

                        //2修改shop_bill的writeIntoDeliveryInfo=1表示已复制到发货表(0表示还未复制)
                        string sql2 = "update shop_bill set WriteIntoDeliveryInfo=1 where billid=" + billId;
                        cmd.CommandText = sql2;
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch (Exception exx)
                {
                    tran.Rollback();
                    throw exx;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 按状态，发货单Id查询未发送的账单
        /// <summary>
        /// 按状态，发货单Id查询未发送的账单
        /// </summary>
        /// <param name="deliveId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public DataTable GetNoDeliveryBillByStatus(int deliveId, int status)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                SqlCommand cmd = new SqlCommand("Proc_GetNoDeliveryBillByStatus", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("deliverId",deliveId));
                cmd.Parameters.Add(new SqlParameter("Status", status));
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 按状态查询未发送的账单
        /// <summary>
        /// 按状态查询未发送的账单
        /// </summary>
        /// <param name="status"></param>
        /// <returns></returns>
        public DataTable GetNoDeliveryBillByStatusOnly(int status)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                SqlCommand cmd = new SqlCommand("Proc_GetNoDeliveryBillByStatusOnly", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("Status", status));
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 从发货单表删除指定的账单
        /// <summary>
        /// 从发货单表删除指定的账单
        /// </summary>
        /// <param name="DelBills"></param>
        public void DelBillFromDeliveryInfo(List<MODEL.GoodsDelivery.BillInfo> DelBills)
        {
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand();
            try
            {
                con.Open();
                SqlTransaction tran = con.BeginTransaction();
                cmd.Connection = con;
                cmd.Transaction = tran;
                try
                {
                    foreach (MODEL.GoodsDelivery.BillInfo bill in DelBills)
                    {
                        int deliveryId = bill.DeliverId;
                        int billId = bill.BillId;
                        cmd.CommandText = string.Format("delete from deliveryInfo where deliverId={0} and billId={1}", deliveryId, billId);
                        cmd.ExecuteNonQuery();

                        string sql2 = "update shop_bill set writeintodeliveryinfo=0 where billid=" + billId;
                        cmd.CommandText = sql2;
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch (Exception exx)
                {
                    tran.Rollback();
                    throw exx;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 获取运输部员工列表
        /// <summary>
        /// 获取运输部员工列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetDeliveryWorkers()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select UserId,UserName from users";
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 修改发货单状态
        /// <summary>
        /// 修改发货单状态
        /// </summary>
        /// <param name="status"></param>
        public void ChangeDeliverStatus(int status,int deliverId,int userId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                //string sql = "update DeliveryInfo set status=" + status + ",userId=" + userId + ",takeAwayTime=getdate() where deliverId=" + deliverId;
                string sql = "update DeliveryInfo set status=" + 2 + ",userId=" + userId + ",deliverTime=GETDATE(),takeAwayTime=GETDATE(),arrivalTime=GETDATE() where deliverId=" + deliverId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        /// <summary>
        /// 将账单置为不需要发货
        /// </summary>
        /// <param name="billIds"></param>
        public void DelNeedDeliver(List<int> billIds)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "update shop_bill set NeedDeliver=0 where billid=";        //0表示不需要发货
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                foreach (int billId in billIds)
                {
                    string sql2 = sql + billId;
                    cmd.CommandText = sql2;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
